package com.zuoye.dao.impl;

import com.zuoye.bean.UserBean;
import com.zuoye.dao.IUserDao;

import java.sql.*;
import java.time.LocalDate;
import java.util.ArrayList;
import java.util.List;

public class UserDaoImpl implements IUserDao {

    @Override
    public void add(UserBean user) {
        //连接对象
        Connection con = null;
        //SQL语句的执行对象
        PreparedStatement ps = null;

        try {
            //加载驱动
            Class.forName("com.mysql.jdbc.Driver");
            con = DriverManager.getConnection("jdbc:mysql://localhost:12345/mygb?characterEncoding=utf-8",
                    "root","lovo");

            ps = con.prepareStatement("insert into t_user(u_name,u_password,u_birthday,u_level)value(?,?,?,?)");

            ps.setObject(1,user.getName());
            ps.setObject(2,user.getPassword());
            ps.setObject(3,user.getBirthday().toString());
            ps.setObject(4,user.getLevel());

            ps.executeLargeUpdate();
        } catch (Exception e) {
            e.printStackTrace();
        }finally {
            try {
                con.close();
                ps.close();
            } catch (SQLException throwables) {
                throwables.printStackTrace();
            }
        }

    }

    @Override
    public void del(int id) {
        Connection con = null;
        PreparedStatement ps = null;

        try {
            Class.forName("com.mysql.jdbc.Driver");
            con = DriverManager.getConnection("jdbc:mysql://localhost:12345/mygb?characterEncoding=utf-8",
                    "root","lovo");
            ps = con.prepareStatement("delete from t_user where pk_userId = ?");
            ps.setObject(1,id);

            ps.executeLargeUpdate();
        } catch (Exception e) {
            e.printStackTrace();
        }finally {
            try {
                con.close();
                ps.close();
            } catch (SQLException throwables) {
                throwables.printStackTrace();
            }
        }
    }

    @Override
    public void update(int id, String password, String level) {
        Connection con = null;
        PreparedStatement ps = null;

        try {
            Class.forName("com.mysql.jdbc.Driver");
            con = DriverManager.getConnection("jdbc:mysql://localhost:12345/mygb?characterEncoding=utf-8",
                    "root","lovo");
            ps = con.prepareStatement("update t_user set u_password=? and u_level=? where pk_userId=?");
            ps.setObject(1,password);
            ps.setObject(2,level);
            ps.setObject(3,id);

            ps.executeLargeUpdate();
        } catch (Exception e) {
            e.printStackTrace();
        }finally {
            try {
                con.close();
                ps.close();
            } catch (SQLException throwables) {
                throwables.printStackTrace();
            }
        }
    }

    @Override
    public List<UserBean> findAll() {
        List<UserBean> list = new ArrayList<>();
        Connection con = null;
        PreparedStatement ps = null;
        ResultSet rs = null;

        try {
            Class.forName("com.mysql.jdbc.Driver");
            con = DriverManager.getConnection("jdbc:mysql://localhost:12345/mygb?characterEncoding=utf-8",
                    "root","lovo");
            ps = con.prepareStatement("select * from t_user");
            rs = ps.executeQuery();

            while (rs.next()){
                UserBean u = new UserBean();
                u.setId(rs.getInt("pk_userId"));
                u.setName(rs.getString("u_name"));
                u.setPassword(rs.getString("u_password"));
                u.setBirthday(LocalDate.parse(rs.getString("u_birthday")));
                u.setLevel(rs.getString("u_level"));
                list.add(u);
            }


        } catch (Exception e) {
            e.printStackTrace();
        }finally {
            try {
                rs.close();
                ps.close();
                con.close();
            } catch (SQLException throwables) {
                throwables.printStackTrace();
            }
        }

        return list;
    }

    @Override
    public List<UserBean> findByName(String name) {
        List<UserBean> list = new ArrayList<>();
        Connection con = null;
        PreparedStatement ps = null;
        ResultSet rs = null;

        try {
            Class.forName("com.mysql.jdbc.Driver");
            con = DriverManager.getConnection("jdbc:mysql://localhost:12345/mygb?characterEncoding=utf-8",
                    "root","lovo");
            ps = con.prepareStatement("select * from t_user where u_name like ?");
            ps.setObject(1,"%"+name+"%");
            rs = ps.executeQuery();
            while (rs.next()){
                UserBean u = new UserBean();
                u.setId(rs.getInt("pk_userId"));
                u.setName(rs.getString("u_name"));
                u.setPassword(rs.getString("u_password"));
                u.setBirthday(LocalDate.parse(rs.getString("u_birthday")));
                u.setLevel(rs.getString("u_level"));
                list.add(u);
            }

        } catch (Exception e) {
            e.printStackTrace();
        }finally {
            try {
                rs.close();
                ps.close();
                con.close();
            } catch (SQLException throwables) {
                throwables.printStackTrace();
            }
        }
        return list;
    }

    @Override
    public List<UserBean> findByBirthday(LocalDate starDate, LocalDate endDate) {
        List<UserBean> list = new ArrayList<>();
        Connection con = null;
        PreparedStatement ps = null;
        ResultSet rs = null;

        try {
            Class.forName("com.mysql.jdbc.Driver");
            con = DriverManager.getConnection("jdbc:mysql://localhost:12345/mygb?characterEncoding=utf-8",
                    "root","lovo");
            ps = con.prepareStatement("select * from t_user where u_birthday>=? and u_birthday<=?");
            ps.setObject(1,starDate.toString());
            ps.setObject(2,endDate.toString());
            rs=ps.executeQuery();

            while (rs.next()){
                UserBean u = new UserBean();
                u.setId(rs.getInt("pk_userId"));
                u.setName(rs.getString("u_name"));
                u.setPassword(rs.getString("u_password"));
                u.setBirthday(LocalDate.parse(rs.getString("u_birthday")));
                u.setLevel(rs.getString("u_level"));
                list.add(u);
            }

        } catch (Exception e) {
            e.printStackTrace();
        }finally {
            try {
                rs.close();
                ps.close();
                con.close();
            } catch (SQLException throwables) {
                throwables.printStackTrace();
            }
        }

        return list;
    }

    @Override
    public List<UserBean> findByLevel(String level) {
        List<UserBean> list = new ArrayList<>();
        Connection con = null;
        PreparedStatement ps = null;
        ResultSet rs = null;

        try {
            Class.forName("com.mysql.jdbc.Driver");
            con = DriverManager.getConnection("jdbc:mysql://localhost:12345/mygb?characterEncoding=utf-8",
                    "root","lovo");
            ps = con.prepareStatement("select * from t_user where u_level=?");
            ps.setObject(1,level);
            rs = ps.executeQuery();

            while (rs.next()){
                UserBean u = new UserBean();
                u.setId(rs.getInt("pk_userId"));
                u.setName(rs.getString("u_name"));
                u.setPassword(rs.getString("u_password"));
                u.setBirthday(LocalDate.parse(rs.getString("u_birthday")));
                u.setLevel(rs.getString("u_level"));
                list.add(u);
            }

        } catch (Exception e) {
            e.printStackTrace();
        }finally {
            try {
                rs.close();
                ps.close();
                con.close();
            } catch (SQLException throwables) {
                throwables.printStackTrace();
            }
        }

        return list;
    }

    @Override
    public List<UserBean> findByLogin(String name, String password) {
        return null;
    }

    public static void main(String[] args) {
        IUserDao dao = new UserDaoImpl();
//        dao.add(new UserBean("喜羊羊","12356",LocalDate.parse("1999-02-03"),"超级用户"));
//        dao.del(1);
//        dao.update(3,"23235","超级用户");
//        System.out.println(dao.findAll());
//        System.out.println(dao.findByName("李文华"));
//        System.out.println(dao.findByBirthday(LocalDate.parse("1999-12-12"),LocalDate.parse("2005-12-12")));
        System.out.println(dao.findByLevel("超级用户"));
    }
}
